package org.simon.myauctioneer.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.simon.myauctioneer.dto.Article;

public class ArticleDAO implements ArticleDAOI {

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;

	public ArticleDAO(String url, String user, String pw) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection con = DriverManager.getConnection(url, user, pw);
			saveStmt = con
					.prepareStatement("INSERT INTO item(brand, family_line, model, condition, description, created_at, valid_until, category, price, sale_type, customer_id) "
							+ "VALUES (?, ?, ?, ?, ?, to_date(?, 'dd-mm-yyyy HH24:MI'), to_date(?, 'dd-mm-yyyy HH24:MI'), ?, ?, ?, ?)");
			loadStmt = con.prepareStatement("SELECT * FROM item "
					+ "WHERE customer_id = ?");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void saveArticle(Article a) {
		try {
			saveStmt.setString(1, a.getBrand());
			saveStmt.setString(2, a.getFamilyLine());
			saveStmt.setString(3, a.getModel());
			saveStmt.setString(4, a.getCondition());
			saveStmt.setString(5, a.getDescription());
			saveStmt.setString(6, a.getCreatedAt());
			saveStmt.setString(7, a.getValidUntil());
			saveStmt.setString(8, a.getCategory());
			saveStmt.setDouble(9, a.getPrice());
			saveStmt.setString(10, a.getSaleType());
			saveStmt.setInt(11, a.getUserId());
			saveStmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public Article loadArticle(int userId) {
		Article a = new Article();
		try {
			loadStmt.setInt(1, userId);
			ResultSet result = loadStmt.executeQuery();
			if (!result.next())
				return null;

			a.setarticleId(result.getInt("item_id"));
			a.setFamilyLine(result.getString("family_line"));
			a.setModel(result.getString("model"));
			a.setCondition(result.getString("condition"));
			a.setDescription(result.getString("description"));
			a.setCreatedAt(result.getString("created_at"));
			a.setValidUntil(result.getString("valid_until"));
			a.setCategory(result.getString("category"));
			a.setPrice(result.getDouble("price"));
			a.setSaleType(result.getString("sale_type"));
			a.setFinalBid(result.getDouble("final_bid"));
			a.setUserId(result.getInt("customer_id"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return a;
	}

	@Override
	public ArrayList<Article> laodArticles(int userId) {
		ArrayList<Article> aAL = new ArrayList<Article>();
		try {
			loadStmt.setInt(1, userId);
			ResultSet result = loadStmt.executeQuery();
			if (!result.next())
				return null;
			
			while(! result.isAfterLast()) {
				Article a = new Article();
				a.setarticleId(result.getInt("item_id"));
				a.setFamilyLine(result.getString("family_line"));
				a.setModel(result.getString("model"));
				a.setCondition(result.getString("condition"));
				a.setDescription(result.getString("description"));
				a.setCreatedAt(result.getString("created_at"));
				a.setValidUntil(result.getString("valid_until"));
				a.setCategory(result.getString("category"));
				a.setPrice(result.getDouble("price"));
				a.setSaleType(result.getString("sale_type"));
				a.setFinalBid(result.getDouble("final_bid"));
				a.setUserId(result.getInt("customer_id"));
				aAL.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return aAL;
	}

}
